package md.sv.dbu.db;

import md.sv.dbu.DBUExecutor;
import md.sv.dbu.config.jaxb.ColumnType;
import md.sv.dbu.config.jaxb.DbConnectionType;
import md.sv.dbu.config.jaxb.UpdateBlockType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * @author MG
 */
public class DBExecutor {
    private static final Logger logger = LoggerFactory.getLogger(DBExecutor.class);

    private final String id;
    private final DataSource dataSource;
    private final NamedParameterJdbcTemplate jdbcTemplate;

    public static String constructInsertSql(String tableName, List<ColumnType> columns) {
        StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
        boolean first = true;
        for (ColumnType column : columns) {
            if (first) {
                first = false;
            } else {
                sql.append(", ");
            }
            sql.append(column.getName());
        }
        sql.append(") VALUES (");
        first = true;
        for (ColumnType column : columns) {
            if (first) {
                first = false;
            } else {
                sql.append(", ");
            }
            sql.append(column.getValue());
        }
        sql.append(")");
        return sql.toString();
    }

    public static String constructUpdateSql(String tableName, String whereClause,  List<ColumnType> columns) {
        StringBuilder sql = new StringBuilder("UPDATE ").append(tableName).append(" SET ");

        boolean first = true;
        for (ColumnType column : columns) {
            if (first) {
                first = false;
            } else {
                sql.append(", ");
            }
            sql.append(column.getName()).append("=").append(column.getValue());
        }

        sql.append(" WHERE ").append(whereClause);
        return sql.toString();
    }

    public static String getSqlFromUpdateBlock(UpdateBlockType updateBlock) {
        if (updateBlock.getInsertSQL() != null) {
            return constructInsertSql(updateBlock.getInsertSQL().getTable(), updateBlock.getInsertSQL().getColumn());
        } else if (updateBlock.getUpdateSQL() != null) {
            return constructUpdateSql(updateBlock.getUpdateSQL().getTable(), updateBlock.getUpdateSQL().getWhere(), updateBlock.getUpdateSQL().getColumn());
        } else {
            return updateBlock.getExecuteSQL().getValue();
        }
    }

    public static void executeBatchUpdateBlock(DBUExecutor dbuExecutor, UpdateBlockType updateBlock, DBExecutor executor, List<MapSqlParameterSource> namedParametersList) {
        String sql;
        DBExecutor updateExecutor;
        if (updateBlock.getInsertSQL() != null) {
            sql = constructInsertSql(updateBlock.getInsertSQL().getTable(), updateBlock.getInsertSQL().getColumn());
            updateExecutor = dbuExecutor.getExecutor(executor, updateBlock.getInsertSQL().getDatabase());
        } else if (updateBlock.getUpdateSQL() != null) {
            sql = constructUpdateSql(updateBlock.getUpdateSQL().getTable(), updateBlock.getUpdateSQL().getWhere(), updateBlock.getUpdateSQL().getColumn());
            updateExecutor = dbuExecutor.getExecutor(executor, updateBlock.getUpdateSQL().getDatabase());
        } else {
            sql = updateBlock.getExecuteSQL().getValue();
            updateExecutor = dbuExecutor.getExecutor(executor, updateBlock.getExecuteSQL().getDatabase());
        }

        boolean result = updateExecutor.executeBatchUpdate(sql, namedParametersList, updateBlock.isLogError());
        if (!result) {
            if (updateBlock.getOnError() != null && !updateBlock.getOnError().isEmpty()) {
                updateExecutor.executeBatchUpdate(updateBlock.getOnError(), namedParametersList, true);
            }
        }

    }

    public static void executeUpdateBlock(DBUExecutor dbuExecutor, UpdateBlockType updateBlock, DBExecutor executor, MapSqlParameterSource namedParameters) {
        String sql;
        DBExecutor updateExecutor;
        if (updateBlock.getInsertSQL() != null) {
            sql = constructInsertSql(updateBlock.getInsertSQL().getTable(), updateBlock.getInsertSQL().getColumn());
            updateExecutor = dbuExecutor.getExecutor(executor, updateBlock.getInsertSQL().getDatabase());
        } else if (updateBlock.getUpdateSQL() != null) {
            sql = constructUpdateSql(updateBlock.getUpdateSQL().getTable(), updateBlock.getUpdateSQL().getWhere(), updateBlock.getUpdateSQL().getColumn());
            updateExecutor = dbuExecutor.getExecutor(executor, updateBlock.getUpdateSQL().getDatabase());
        } else {
            sql = updateBlock.getExecuteSQL().getValue();
            updateExecutor = dbuExecutor.getExecutor(executor, updateBlock.getExecuteSQL().getDatabase());
        }

        boolean result = updateExecutor.executeUpdate(sql, namedParameters, updateBlock.isLogError());
        if (!result) {
            if (updateBlock.getOnError() != null && !updateBlock.getOnError().isEmpty()) {
                updateExecutor.executeUpdate(updateBlock.getOnError(), namedParameters, true);
            }
        }
    }

    public DBExecutor(DbConnectionType connectionType) {
        this.id = connectionType.getId();
        this.dataSource = getDataSource(connectionType);
        this.jdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
    }

    private DataSource getDataSource(DbConnectionType connectionType) {
        SingleConnectionDataSource dataSource = new SingleConnectionDataSource();
        dataSource.setDriverClassName(connectionType.getDriver());
        dataSource.setUrl(connectionType.getUrl());
        dataSource.setUsername(connectionType.getUser());
        dataSource.setPassword(connectionType.getPassword());
        dataSource.setAutoCommit(false);

        return dataSource;
    }

    public void close() {
        try {
            DataSourceUtils.getConnection(dataSource).close();
        } catch (SQLException e) {
            logger.error("Could not close connection ID=" + this.id, e);
        }
    }

    /**
     * Execute a Select query and populates a collection of SqlParameterSource objects
     *
     * @param query         the select query to execute
     * @param params        the parameters of sql query, or {@code null} if query has no parameters
     * @param globalVars    the global variables map (to be added to the parameters)
     *
     * @return              a Collection of {@link SqlParameterSource} with the size = to the number of rows returned by the query
     */
    public List<MapSqlParameterSource> selectQuery(String query, SqlParameterSource params, final Map<String, ?> globalVars) {
        final List<MapSqlParameterSource> parameterSources = new ArrayList<MapSqlParameterSource>();

        if (params == null) {
            params = EmptySqlParameterSource.INSTANCE;
        }

        try {
            jdbcTemplate.query (query, params, new RowCallbackHandler() {
                @Override
                public void processRow(ResultSet rs) throws SQLException {
                    parameterSources.add(getParameterSource(rs, globalVars));
                }
            });
        } catch (DataAccessException e) {
            logger.error("Could not execute SELECT [{}] for database {}: {}", query, this.id, e);
        }

        return parameterSources;
    }

    private MapSqlParameterSource getParameterSource(ResultSet rs, Map<String, ?> globalVars) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int colCount = metaData.getColumnCount();
        MapSqlParameterSource parameterSource = new MapSqlParameterSource(globalVars);
        for (int i = 1; i <= colCount; i++) {
            parameterSource.addValue(metaData.getColumnName(i), rs.getObject(i));
        }
        return parameterSource;
    }

    public boolean executeBatchUpdate(String statement, Collection<? extends SqlParameterSource> namedParams, boolean logError) {
        try {
            this.jdbcTemplate.batchUpdate(statement, namedParams.toArray(new SqlParameterSource[namedParams.size()]));
            return true;
        } catch (Exception e) {
            if (logError) {
                logger.error("Could not execute Batch Update [{}] for database {}: {} ", statement, this.id, e);
                logger.error(statement);
            }
        }
        return false;
    }

    public boolean executeUpdate(String statement, SqlParameterSource namedParams, boolean logError) {
        try {
            this.jdbcTemplate.update(statement, namedParams);
            return true;
        } catch (DataAccessException e) {
            if (logError) {
                logger.error("Could not execute Update [{}] for database {}: {} ", statement, this.id, e);
                logger.error(statement);
            }
        }
        return false;
    }

    public void commit() {
        try {
            DataSourceUtils.getConnection(this.dataSource).commit();
        } catch (SQLException e) {
            logger.error("Could not commit ID=" + this.id, e);
        }
    }

    public void rollback() {
        try {
            DataSourceUtils.getConnection(this.dataSource).rollback();
        } catch (SQLException e) {
            logger.error("Could not rollback ID=" + this.id, e);
        }
    }

    public NamedParameterJdbcTemplate getTemplate() {
        return jdbcTemplate;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

}
